import pandas as pd
import numpy as np
import pymysql
'''
Pandas：Python最流行的数据处理与数据分析的类库
SQL：结构化查询语言，用于对MySQL、Oracle等关系型数据库的增删改查
'''
def dateframeCompareSql():
    df = pd.read_csv("../datas/titanic/titanic_train.csv")
    # 1. SELECT数据查询¶
    # SQL：
    sql = """
        SELECT PassengerId, Sex, Age, Survived
        FROM titanic
        LIMIT 5;
    """
    # pandas
    print(df[['PassengerId', 'Sex', 'Age', 'Survived']].head(5))
    # 2. WHERE按条件查询
    # SQL：
    sql = """
        SELECT *
        FROM titanic
        where Sex='male' and Age>=20.0 and Age<=40.0
        LIMIT 5;
    """
    condition = (df['Sex'] == 'male') & (df['Age'] >= 20.0) & df['Age'] <= 40.0
    print(df.loc[condition].head(5))

    # 3. in和not in的条件查询
    # SQL：
    sql = """
        SELECT *
        FROM titanic
        where Pclass in (1,2)
        LIMIT 5;
    """
    condition = df['Pclass'].isin((1, 2))
    print(df[condition].head())
    # not in
    print(df[~condition].head())

    # 4. groupby分组统计
    # 4.1 单个列的聚合
    # SQL：
    sql = """
        SELECT 
            -- 分性别的存活人数
            sum(Survived),
            -- 分性别的平均年龄
            mean(Age)
            -- 分性别的平均票价
            mean(Fare)
        FROM titanic
        group by Sex
    """
    df.groupby('Sex').agg({'Survived':np.sum,'Age':np.mean,'Fare':np.mean})

    # 4.2 多个列的聚合
    # SQL：
    sql = """
        SELECT 
            -- 不同存活和性别分组的，平均年龄
            mean(Age)
            -- 不同存活和性别分组的，平均票价
            mean(Fare)
        FROM titanic
        group by Survived, Sex
    """
    df.groupby(['Survived','Sex']).agg({'Survived':np.mean,'Fare':np.mean})

    # 5. JOIN数据关联
    # 电影评分数据集，评分表
    df_rating = pd.read_csv("../datas/ml-latest-small/ratings.csv")
    df_rating.head(5)

    # 电影评分数据集，电影信息表
    df_movies = pd.read_csv("../datas/ml-latest-small/movies.csv")
    df_movies.head(5)

    # SQL：
    sql = """
        SELECT *
        FROM 
            rating join movies 
            on(rating.movieId=movies.movieId)
        limit 5
    """
    pd.merge(left=df_rating,right=df_movies,on='movieId')

    # 6. UNION数据合并
        # SQL：
    sql = """
        SELECT city, rank
        FROM df1
        
        UNION ALL
        
        SELECT city, rank
        FROM df2;
    """
    df1 = pd.DataFrame({'city': ['Chicago', 'San Francisco', 'New York City'],
                        'rank': range(1, 4)})
    df2 = pd.DataFrame({'city': ['Chicago', 'Boston', 'Los Angeles'],
                        'rank': [1, 4, 5]})
    pd.concat([df1,df2])

    # 7. Order Limit先排序后分页
    # SQL：
    sql = """
        SELECT *
        from titanic
        order by Fare
        limit 5
    """
    df.sort_values("Fare",ascending=True).head(5)

    # 8. 取每个分组group的top n
    # MYSQL不支持
    # Oracle有ROW_NUMBER语法

    # 按（Survived，Sex）分组，取Age的TOP 2
    df.groupby(["Survived", "Sex"]).apply(
        lambda df:df.sort_values("Age", ascending=False).head(2))

    # 9. UPDATE数据更新
    # SQL：
    sql = """
        UPDATE titanic
        set Age=0
        where Age is null
    """
    condition = df["Age"].isna()
    print(condition.value_counts())
    # 设置age为空 的默认值为 0
    df[condition] = 0
    print(df["Age"].isna().value_counts())

    # 10. DELETE删除数据
    # SQL：
    sql = """
        DELETE FROM titanic
        where Age=0
    """
    df_new = df[df["Age"]!=0]

    df_new[df_new["Age"]==0]








def getDb():
    # 打开数据库连接
    db = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="pandas" )
    return db
def add_data():
    df = pd.read_csv("../datas/titanic/titanic_train.csv")
    df['Cabin'] = df['Cabin'].fillna(0)
    df['Name'] = df['Name'].str.replace('"',"")
    df['Age'] = df['Age'].fillna(0)
    dflist = df.values.tolist()
    db = getDb()
    cursor = db.cursor()
    sql = "insert into titanic (passengerId,survived,pclass,name,sex,age,sibSp,parch,ticket,fare,cabin,embarked) values ({0},'{1}','{2}','{3}','{4}',{5},'{6}','{7}','{8}','{9}','{10}','{11}');"
    # PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
    print("数据量",len(dflist))
    for x in dflist:
        # print(x[10])
        # print(x[5])
        ss =sql.format(x[0], x[1], x[2],x[3],x[4],x[5],x[6],x[7],x[8],x[9],x[10],x[11])
        cursor.execute(str(ss))
        # print(ss)

if __name__ =='__main__':
    # add_data()
    dateframeCompareSql()
